-- Created		06/02/2009
-- Modified	
-- Author		wilfried.vandenberghe@gmail.com
-- Version		1.0
-- Database	Oracle 10g 

-- NOTIFICATION type definition.
-- A simple e-mail notification.
CREATE TYPE NOTIF AS OBJECT 
(
	-- SMTP server address
	mailhost	VARCHAR2(254),
	-- to addresses
	to_address 	 CHARLIST,
	-- from address
	from_address VARCHAR2(254),
	-- Notification subject
	subject VARCHAR2(254),
	-- Content
	notif_content CONTENT,
		
	-- 
	-- Constructor from a content
	-- 
	CONSTRUCTOR FUNCTION NOTIF(c CONTENT) RETURN SELF AS RESULT,
	
	--
	-- Set the SMTP server address
	--
	MEMBER PROCEDURE set_mailhost(host VARCHAR2),

	-- 
	-- Attaches a content
	-- 
	MEMBER PROCEDURE set_content(c CONTENT),
	
	-- 
	-- Set the subject
	-- 
	MEMBER PROCEDURE set_subject(subject VARCHAR2),
	
	--
	-- Set the from address
	--
	MEMBER PROCEDURE set_from_address(address VARCHAR2),
	
	-- 
	-- Provides a preview of the e-mail content.
	-- 
	MEMBER FUNCTION preview RETURN LONG,
	
	-- 
	-- Send the mail.
	-- 
	MEMBER PROCEDURE send,
	
	-- 
	-- Add a to address
	-- 
	MEMBER PROCEDURE add_to(address VARCHAR2)
); 
/

CREATE TYPE BODY NOTIF AS
	
	CONSTRUCTOR FUNCTION NOTIF(c CONTENT) RETURN SELF AS RESULT AS
	BEGIN
		SELF.notif_content := c;
		RETURN;
	END NOTIF;
	
	MEMBER PROCEDURE set_mailhost(host VARCHAR2) IS
	BEGIN
		SELF.mailhost := host;
	END set_mailhost;

	MEMBER PROCEDURE set_content(c CONTENT) IS
	BEGIN
		SELF.notif_content := c;
	END set_content;
	
	MEMBER PROCEDURE set_subject(subject VARCHAR2) IS
	BEGIN
		SELF.subject := subject;
	END set_subject;
	
	MEMBER PROCEDURE set_from_address(address VARCHAR2) IS
	BEGIN
		SELF.from_address := address;
	END;
	
    MEMBER FUNCTION preview RETURN LONG IS
		res LONG;
    BEGIN
		res := notif_content.format;
		RETURN (res);
    END preview;
   
    MEMBER PROCEDURE send IS
		mail_conn   UTL_SMTP.connection;
		address		INTEGER;
    BEGIN
		-- open a connection
		mail_conn := UTL_SMTP.open_connection(mailhost, 25);
		-- data for mail header
		UTL_SMTP.helo(mail_conn, mailhost);
		UTL_SMTP.mail(mail_conn, from_address);
		
		-- build the to address list
		FOR address IN SELF.to_address.FIRST .. SELF.to_address.LAST
		LOOP
			UTL_SMTP.rcpt(mail_conn, SELF.to_address(address));
		END LOOP;
		
		UTL_SMTP.open_data(mail_conn);
		
		-- set mail settings
		UTL_SMTP.write_data(mail_conn, 'MIME-version: 1.0' || UTL_TCP.CRLF);
		UTL_SMTP.write_data(mail_conn, 'Content-Type: text/plain; charset=utf-8'|| UTL_TCP.CRLF);
		UTL_SMTP.write_data(mail_conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.CRLF);
		UTL_SMTP.write_data(mail_conn, 'SUBJECT: ' || SELF.subject || UTL_TCP.CRLF);
		
		-- fill content
		UTL_SMTP.write_raw_data(mail_conn, UTL_RAW.cast_to_raw(UTL_TCP.CRLF || notif_content.format));
		-- close content
		UTL_SMTP.close_data(mail_conn);
		-- send mail and close connection
		UTL_SMTP.quit(mail_conn);
	EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
		utl_smtp.quit(mail_conn);
      RAISE;
    WHEN others THEN
      RAISE;
   END send;
   
   MEMBER PROCEDURE add_to(address VARCHAR2) AS
   BEGIN
		IF SELF.to_address IS NULL THEN
			SELF.to_address := CHARLIST(address);
		ELSE
			SELF.to_address.extend();
			SELF.to_address(SELF.to_address.count()) := address;
		END IF;
   END add_to;
END;
/
